package univer.oko.database.rmi.impl;

import univer.oko.database.CDPreparedStatement;
import univer.oko.database.rmi.RMILogger;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Vector;

/**
 * @author Eduard Dudar
 *         Date: 21.11.2005
 *         Time: 0:11:18
 */
public class ImplHelper {
    private Statement st;
    public static final String INSERT_INTO = " insert into ";
    public static final String SELECT = " select ";
    public static final String ALL = " * ";
    public static final String DELETE_FROM = " delete from ";
    public static final String FROM = " from ";
    public static final String WHERE = " where ";
    public static final String AND = " and ";
    public static final String OR = " or ";
    public static final String ORDER_BY = " order by ";
    public static final String GROUP_BY = " group by ";
    public static final String IN = " in ";
    public static final String LIKE = " like ";
    public static final String NOT = " not ";

    public static final String MAX(String column) {
        return " max(" + column + ") ";
    }

    public static final String SUM(String column) {
        return " sum(" + column + ") ";
    }

    protected RMIImpl rmi;

    public ImplHelper(RMIImpl rmi) {
        this.rmi = rmi;
    }

    public CallableStatement prepareCall(String sql) throws SQLException {
        return rmi.getConnection().prepareCall(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    }

    public Statement createStatement() throws SQLException {

        if (st == null) {
            st = rmi.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        }
        return st;
    }

    public CDPreparedStatement prepareStatement(String sql) throws SQLException {
        // wrap prepared statement in order to handle nulls for foreign keys
        return new CDPreparedStatement(this.rmi.getConnection().prepareStatement(
                sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
    }

    public void commit() {
        try {
            this.rmi.getConnection().commit();
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
        }
    }

    public void rollback() {
        try {
            this.rmi.getConnection().rollback();
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
        }
    }

    protected void close() {
        try {
            this.rmi.getConnection().close();
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
        }
    }

    public Integer getIntValue(CDPreparedStatement pst) {
        try {
            ResultSet rsSet = pst.executeQuery();
            if (rsSet.next()) {
                return rsSet.getInt(1);
            }
            return null;
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
            return univer.oko.Error.SQL_ERROR;
        }
    }

    public Integer getIntValue(String query) {
        try {
            ResultSet rsSet = this.createStatement().executeQuery(query);
            if (rsSet.next()) {
                return rsSet.getInt(1);
            }
            return null;
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
            return univer.oko.Error.SQL_ERROR;
        }
    }

    public Double getDoubleValue(String query) {
        try {
            ResultSet rsSet = this.createStatement().executeQuery(query);
            if (rsSet.next()) {
                return rsSet.getDouble(1);
            }
            return null;
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
            return (double) univer.oko.Error.SQL_ERROR;
        }
    }

    public String getStringValue(String query) {
        try {
            ResultSet rsSet = this.createStatement().executeQuery(query);
            if (rsSet.next()) {
                return rsSet.getString(1);
            }
            return null;
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
            return null;
        }
    }

    public String getStringsValue(String query) {
        try {
            ResultSet rsSet = this.createStatement().executeQuery(query);
            String str;
            while (rsSet.next()) {
                str = rsSet.getString(1);
            }
            return null;
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
            return null;
        }
    }


    public ArrayList<Integer> getIntListValue(String query) {
        try {
            ResultSet rsSet = this.createStatement().executeQuery(query);
            ArrayList<Integer> ret = new ArrayList<Integer>(rsSet.getFetchSize());
            while (rsSet.next()) {
                ret.add(rsSet.getInt(1));
            }
            return ret;
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
            return null;
        }
    }

    public Vector getVectorValue(String query) {
        try {
            ResultSet rsSet = this.createStatement().executeQuery(query);
            Vector ret = new Vector(rsSet.getFetchSize());
            while (rsSet.next()) {
                ret.add(rsSet.getString(1));
            }
            return ret;
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
            return null;
        }
    }

    public ArrayList<String> getArrayListValue(String query) {
        try {
            ResultSet rsSet = this.createStatement().executeQuery(query);
            ArrayList<String> ret = new ArrayList<String>();
            while (rsSet.next()) {
                ret.add(rsSet.getString(1));
            }
            return ret;
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
            return null;
        }
    }

    public boolean isOccurs(String query) {
        try {
            ResultSet rsSet = this.createStatement().executeQuery(query);
            return rsSet.next();
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
            return false;
        }
    }

    public boolean isOccurs(CDPreparedStatement pst) {
        try {
            ResultSet rsSet = pst.executeQuery();
            return rsSet.next();
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
            return false;
        }
    }

    public boolean executeUpdate(String query) {
        try {
            return (this.prepareStatement(query).executeUpdate() > 0) ? true : false;
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
            return false;
        }
    }

    public boolean executeUpdate(CDPreparedStatement pst) {
        try {
            return (pst.executeUpdate() > 0) ? true : false;
        }
        catch (Exception e) {
            RMILogger.ServerLog(e);
            return false;
        }
    }

    public void closeConnection() {
        this.commit();
        this.close();
    }

    protected Integer getPaymentYearIndex(java.util.Date date) {
        Calendar c = Calendar.getInstance();
        c.setTime(date);
        return (c.get(Calendar.MONTH) < Calendar.JULY)
                ? this.getIntValue(
                "select [id_paymentyear] " +
                        "from [payment_year] " +
                        "where [paymentyear_name] = '" + (c.get(Calendar.YEAR) - 1) + "/" + c.get(Calendar.YEAR) + "'")
                : this.getIntValue(
                "select [id_paymentyear] " +
                        "from [payment_year] " +
                        "where [paymentyear_name] = '" + c.get(Calendar.YEAR) + "/" + (c.get(Calendar.YEAR) + 1) + "'");
    }
}
